import pymysql
import logging
import time
import sys
from apscheduler.schedulers.blocking import BlockingScheduler

from DBUtils.PooledDB import PooledDB
#定义连接池
poolcamp = PooledDB(pymysql, 5, host='localhost', user='root', passwd='czc', db='test', port=3306)  # 5为连接池里的最少连接数
pool = PooledDB(pymysql, 5, host='localhost', user='root', passwd='czc', db='test', port=3306)  # 5为连接池里的最少连接数

#从camp表获取数据，插入到本地表中
def insertData():
    conncamp = poolcamp.connection()  # 以后每次需要数据库连接就是用connection（）函数获取连接就好了
    conn = pool.connection()
    curcamp = conncamp.cursor()
    cur = conn.cursor()

    SQL = "select id,name,phone,telephone,address,enabled,username,password,userface,remark from hr"
    r = curcamp.execute(SQL)
    r = curcamp.fetchall()

    for val in r:
        cur.execute("insert into hrd(id,name,phone,telephone,address,enabled,username,password,userface,remark) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s )",val)
    conn.commit()

    cur.close()
    conn.close()
    curcamp.close()
    conncamp.close()
#定时执行sql
def executeSqlTimed(SQL):
    #SQL = "insert into hrd(id,name,phone,telephone,address,enabled,username,password,userface,remark) select id,name,phone,telephone,address,enabled,username,password,userface,remark from hr"
    #logging.info("定时执行sql：" + SQL)
    conn = pool.connection()
    cur = conn.cursor()
    cur.execute(SQL)
    cur.close()
    conn.close()

logging.basicConfig(filename=sys.argv[0][0:-3]+'.log', format='[%(asctime)s-%(filename)s-%(levelname)s:%(message)s]', level = logging.INFO, filemode='w', datefmt='%Y-%m-%d %I:%M:%S %p')

# 调度时间的配置方式参考文档 ： http://jinbitou.net/2016/12/19/2263.html
scheduler = BlockingScheduler()
#间隔5秒钟执行一次
# scheduler.add_job(func=executeSqlTimed, args=('insert into hrd(id,name,phone,telephone,address,enabled,username,password,userface,remark) select id,name,phone,telephone,address,enabled,username,password,userface,remark from hr',), trigger='cron', second='*/5')
#间隔5秒钟执行一次   day : 周一到周五  hour:9-17  minutes:每分钟
scheduler.add_job(insertData, 'cron',day_of_week='mon-fri', hour='8-16', minute ='*/1')
scheduler.start()